/**
 * 上海中赢金融信息服务有限公司
 * Copyright (c) 2017-2027 Chinazyjr,Inc.All Rights Reserved.
 */
package com.sys.midware.rdb.handler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;

import com.sys.midware.logger.Logger;
import com.sys.midware.logger.LoggerFactory;
import com.sys.midware.rdb.datasource.BDataSource;
import com.sys.midware.rdb.exception.RDBException;
import com.sys.midware.rdb.handler.op.DeleteParams;
import com.sys.midware.rdb.handler.op.InsertParams;
import com.sys.midware.rdb.handler.op.QueryParams;
import com.sys.midware.rdb.handler.op.UpdateParams;

/**
 * 实现HBase表操作的类 <b>ClassName：</b> HBaseDBHandler <br/>
 * <b>@author：</b> mobing <br/>
 * <b>@date：</b> 2015年10月28日 下午2:59:30 <br/>
 * <b>@version: </b> <br/>
 */
public class RDBHandlerJDBC  implements IRDBHandlerJDBC{

    private static Logger logger = LoggerFactory.getLogger(RDBHandlerJDBC.class);

//    private Statement stmt;
//
//    private PreparedStatement pstmt;
//
//    private ResultSet rs;

//    private Connection connection;

    private BDataSource pool;
    
    
    public RDBHandlerJDBC(BDataSource pool) {
        this.pool = pool;
//        this.pool.getConnection();
    }

    public void performInsert(InsertParams<?> ip) throws RDBException {
        // InsertParams<?> ip = (InsertParams<?>) record;
        PreparedStatement pstmt=null;
        Connection connection =null;
        try {
            connection = this.pool.getConnection();
            Map<String, ?> params = ip.getParams();
            ArrayList<Object> paramArray = new ArrayList<Object>();

            StringBuffer bf = new StringBuffer();
            bf.append("INSERT INTO ");
            bf.append(ip.getTableName());
            bf.append(" (");

            StringBuilder values = new StringBuilder(" VALUES (");

            // loop for cols
            Iterator it = params.entrySet().iterator();
            boolean first = true;
            while (it.hasNext()) {
                Map.Entry e = (Map.Entry) it.next();
                String key = (String) e.getKey();

                Object o = e.getValue();
                if (o != null) {
                    paramArray.add(o);

                    if (!first) {
                        bf.append(',');
                        values.append(',');
                    }
                    bf.append(key);
                    values.append('?');

                    first = false;
                }
            }

            bf.append(')');
            values.append(')');
            bf.append(values);

            pstmt = connection.prepareStatement(bf.toString());
            if (paramArray.size() > 0) {
                int idx = 1;
                for (Object o : paramArray) {
                    pstmt.setObject(idx, o);
                    idx++;
                }
            }

            pstmt.execute();
        } catch (Exception e) {
            throw new RDBException("performInsert error!",e);
        } finally {
            close(pstmt,connection);
        }
    }

    public void performInsertBatch(List<InsertParams<?>> records) throws RDBException {
        Connection connection = null;
        try {
            connection = this.pool.getConnection();
            
            boolean oldAutoCommitMode =  connection.getAutoCommit();// 保存原来的自动提交模式
            connection.setAutoCommit(false);// 不要自动提交

            for (int i = 0; i < records.size(); i++) {
                this.performInsert(records.get(i));
            }

            connection.commit();// 手动触发提交
            connection.setAutoCommit(oldAutoCommitMode);// 恢复原来的自动提交模式
        } catch (SQLException e) {
            
            throw new RDBException("performInsertBatch error!",e);
            
        }finally{
            close(connection);
        }
    }

    public void performUpdate(UpdateParams<?, ?> up)  {
        // UpdateParams<?, ?> up = (UpdateParams<?, ?>) record;
       
        String tableName = up.getTableName();
        Map<String, ?> fields = up.getUpdateFields();

        if (tableName == null || fields == null || fields.size() == 0)
            throw new RDBException("Invalid SQL statement for postgresql update!");
        Connection connection =null;
        PreparedStatement pstmt=null;
        try {
            connection = this.pool.getConnection();
            String whereClause = up.getWhereClause();
            List<?> whereParams = up.getWhereParams();

            List<Object> paramArray = new ArrayList<Object>();
            StringBuilder sb = new StringBuilder("update ");
            sb.append(tableName).append(" set ");

            Iterator it = fields.entrySet().iterator();
            boolean first = true;
            while (it.hasNext()) {
                Map.Entry e = (Map.Entry) it.next();
                String key = (String) e.getKey();

                Object o = e.getValue();

                if (!first) {
                    sb.append(',');
                }
                sb.append(key);
                sb.append('=');
                if (o == null) {
                    sb.append("NULL");
                } else {
                    sb.append('?');
                    paramArray.add(o);
                }

                first = false;
            }

            if (whereClause != null) {
                sb.append(' ');
                sb.append(whereClause);
                if (whereParams != null) {
                    for (int j = 0; j < whereParams.size(); j++) {
                        Object value = whereParams.get(j);
                        paramArray.add(value);
                    }
                }
            }
            pstmt = connection.prepareStatement(sb.toString());
            setParametersIntoPreparedStatement(paramArray, pstmt);
            pstmt.execute();
        } catch (Exception e) {
            throw new RDBException("performUpdate error!",e);
        } finally {
            close(pstmt,connection);
        }

    }

    public void performUpdateBatch(List<UpdateParams<?, ?>> records) throws RDBException {
        for (int i = 0; i < records.size(); i++) {
            this.performUpdate(records.get(i));
        }
    }

    public void performDeleteBatch(List<DeleteParams<?>> records) throws RDBException {
        for (int i = 0; i < records.size(); i++)
            this.performDelete(records.get(i));
    }

    public void performDelete(DeleteParams<?> record) throws RDBException {
        DeleteParams<?> dp = (DeleteParams<?>) record;
        String tableName = dp.getTableName();
        String whereClause = dp.getWhereClause();
        List<?> whereParams = dp.getWhereParams();
         Statement stmt = null;
         Connection connection = null;
         PreparedStatement pstmt=null;
        try {
            connection = this.pool.getConnection();
            StringBuilder sb = new StringBuilder("delete from ");
            sb.append(tableName);
            if (whereClause != null) {
                sb.append(" ").append(whereClause);
                pstmt = connection.prepareStatement(sb.toString());
                for (int j = 0; j < whereParams.size(); j++) {
                    pstmt.setObject((j + 1), whereParams.get(j));
                }
                stmt = pstmt;
                pstmt.execute();
            } else {
                stmt = connection.createStatement();
                stmt.execute(sb.toString());
            }

        } catch (Exception e) {

        } finally {
            close(stmt,pstmt,connection);
        }

    }

    public Object performQuery(QueryParams<?> qp) throws RDBException {
        if (qp == null)
            throw new RDBException("the query string is null!");

        // QueryParams<?> qp = (QueryParams<?>) obj;
        Connection connection =null;
        Statement stmt = null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            connection = this.pool.getConnection();
            String queryStr = qp.getQueryStr();
            List<?> params = qp.getParams();
            if (queryStr == null)
                throw new SQLException("the query string is null!");

          
            if (params == null || params.size() == 0) {
                stmt = connection.createStatement();

                rs = stmt.executeQuery(queryStr);

                return rs;
            } else {
                pstmt = connection.prepareCall(queryStr);
                // 设置参数
                for (int i = 0; i < params.size(); i++) {
                    Object o = params.get(i);
                    pstmt.setObject((i + 1), o);
                }
                return pstmt.executeQuery();
            }
        } catch (Exception e) {
            throw new RDBException("performQuery error!",e);

        } finally {
            close(rs,stmt,pstmt,connection);
        }
    }
    
    public <E extends Object>Collection<E> performQuery4List(QueryParams<?> qp) throws RDBException {
        if (qp == null)
            throw new RDBException("the query string is null!");

        // QueryParams<?> qp = (QueryParams<?>) obj;
        Statement stmt = null;
        Connection connection =null;
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        try {
            connection =this.pool.getConnection();
            String queryStr = qp.getQueryStr();
            List<?> params = qp.getParams();
            if (queryStr == null)
                throw new SQLException("the query string is null!");

          
            if (params == null || params.size() == 0) {
                stmt = connection.createStatement();

                rs = stmt.executeQuery(queryStr);
                
                return null;
            } else {
                pstmt = connection.prepareCall(queryStr);
                // 设置参数
                for (int i = 0; i < params.size(); i++) {
                    Object o = params.get(i);
                    pstmt.setObject((i + 1), o);
                }
                rs =pstmt.executeQuery();
                return null;
            }
        } catch (Exception e) {
            throw new RDBException("performQuery error!",e);

        } finally {
            close(rs,stmt,pstmt,connection);
        }
    }

//    public void performDrop(String obj) throws RDBException {
//        // Statement stmt = null;
//        try {
//            stmt = this.connection.createStatement();
//            stmt.execute("DROP TABLE " + obj.toString());
//        } catch (Exception e) {
//            throw new RDBException("performDrop error!",e);
//        } finally {
//            close();
//        }
//    }

    private void setParametersIntoPreparedStatement(List<Object> paramArray, PreparedStatement ps) throws SQLException {
        if (CollectionUtils.isEmpty(paramArray) || ps == null) {
            return;
        }

        int parameterIndex = 0;
        for (Object param : paramArray) {
            Class<? extends Object> paramClass = param.getClass();
            if (paramClass.equals(Integer.class)) {
                ps.setInt(++parameterIndex, (Integer) param);
            } else if (paramClass.equals(Long.class)) {
                ps.setLong(++parameterIndex, (Long) param);
            } else if (paramClass.equals(String.class)) {
                ps.setString(++parameterIndex, (String) param);
            } else if (paramClass.equals(Boolean.class)) {
                ps.setBoolean(++parameterIndex, (Boolean) param);
            } else {
                ps.setObject(++parameterIndex, param);
            }
        }
    }

    // 释放对象
//    private void close() throws RDBException {
//        try {
//            if (rs != null) {
//                rs.close();
//            }
//            if (stmt != null) {
//                stmt.close();
//            }
//            if (pstmt != null) {
//                pstmt.close();
//            }
//            if (connection != null) {
//                connection.close();
//            }
//        } catch (SQLException e) {
//            logger.error("close error!");
//        }
//    }

    
  //释放对象
    private void close(ResultSet rs, Statement stmt, PreparedStatement pstmt, Connection conn) throws RDBException {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            logger.error("close rs、stmt、pstmt、conn etc.  error!");
        }
    }
    
    // 释放对象
    private void close( Statement stmt, PreparedStatement pstmt, Connection conn) throws RDBException {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            logger.error("close rs、stmt、pstmt、conn etc.  error!");
        }
    }
    
 // 释放对象
    private void close( PreparedStatement pstmt, Connection conn) throws RDBException {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            logger.error("close rs、stmt、pstmt、conn etc.  error!");
        }
    }
    
    // 释放对象
    private void close( Connection conn) throws RDBException {
        try {
           
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            logger.error("close rs、stmt、pstmt、conn etc.  error!");
        }
    }
}
